package db.document;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import db.Database;
import db.history.SystemLogDB;
import db.history.经营情况DB;
import po.AccountBookPo;
import po.DocumentPo;
import po.Po;
import po.SystemLogPo;
import po.库存赠送单po;
public class StockSendingDocumentDB extends DocumentDB{
	
	

	public StockSendingDocumentDB(AccountBookPo ab) {
		super(ab);
		String createT_giftDoc="CREATE TABLE IF NOT EXISTS `seac2`.`"+book.getName()+ "_Doc_gift` ( `f_id` varchar(40) NOT NULL,`f_client` varchar(40) NOT NULL, `f_commodityID` varchar(20) NOT NULL, `f_commodityName` varchar(20) NOT NULL, `f_commodityModel` varchar(20) NOT NULL, `f_giftNum` int(8) NOT NULL,`f_date` date NOT NULL, `f_isApproved` int(1) NOT NULL  DEFAULT  '0', `f_remark` varchar(50) , PRIMARY KEY (`f_id`),UNIQUE KEY `f_id` (`f_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8";
		Connection c = Database.getConnection();
		Statement s;
		try {
			s = c.createStatement();
			s.execute(createT_giftDoc);
			s.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		// TODO Auto-generated constructor stub
	}

	@Override
	public Po add(DocumentPo po) {
		Po m=new Po();int total_num=0;
		Connection c = Database.getConnection();
		String id;int stock=0;
		String client=((库存赠送单po) po).get客户();
		String commodityID=((库存赠送单po) po).get商品编号();
		String commodityName=((库存赠送单po) po).get商品名称();
		String commodityModel=((库存赠送单po) po).get商品型号();
		int num=((库存赠送单po) po).getNum();
		String remark=((库存赠送单po) po).get备注()==null? "":((库存赠送单po) po).get备注();
		String date=new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		try {
			Statement s = c.createStatement();
			ResultSet rs=s.executeQuery("select * from "+book.getName()+"_Doc_gift");
			while(rs.next()){
				total_num++;
			}
		    id=getDocumentId("KCZSD",total_num);
		    ResultSet rs1=s.executeQuery("select f_name,f_model,f_stockNum from "+book.getName()+"_commodity where f_commodityID='"+commodityID+"'");
		    while(rs1.next()){
		    	commodityName=rs1.getString(1);
		    	commodityModel=rs1.getString(2);
		    	stock=rs1.getInt(3);
		    }
		    if((commodityID.contains("代金券")==false)&&stock<num){
		    	m.failed("您填写的"+commodityName+"赠送数量大于库存数量！");
		    	return m;
		    }
			s.execute("insert into "+book.getName()+"_Doc_gift(f_id,f_client,f_commodityID,f_commodityName,f_commodityModel,f_giftNum,f_date,f_remark) values('"+id+"','"+client+"','"+commodityID+"','"+commodityName+"','"+commodityModel+"','"+num+"','"+date+"','"+remark+"')");
			m.successed();
			//添加日志
			SystemLogDB log=new SystemLogDB();
			SystemLogDB.iniSystemLogDB();
			log.add(new SystemLogPo(po.get操作员(),"add",id));
			
			s.close();
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		return m;
	}

	@Override
	public Po update(DocumentPo po) {
		Po m=new Po();
		String doc_id=((库存赠送单po) po).get单据编号();
		Connection c = Database.getConnection();
		String client=((库存赠送单po) po).get客户();
		String commodityID=((库存赠送单po) po).get商品编号();
		String commodityName=((库存赠送单po) po).get商品名称();
		String commodityModel=((库存赠送单po) po).get商品型号();
		int num=((库存赠送单po) po).getNum();
		String remark=((库存赠送单po) po).get备注();
		try {
			Statement s = c.createStatement();
			s.execute("update "+book.getName()+"_Doc_gift set f_client='"+client+"',f_commodityID='"+commodityID+"',f_commodityName='"+commodityName+"',f_commodityModel='"+commodityModel+"',f_giftNum='"+num+"',f_remark='"+remark+"' where f_id='"+doc_id+"'");
			m.successed();
			s.close();
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		return m;
	}

	@Override
	public List<DocumentPo> getUnapproval() {
		List<DocumentPo> list=new ArrayList<DocumentPo>();
		Connection c=Database.getConnection();
		Statement s;
		try {
			s = c.createStatement();
			ResultSet rs=s.executeQuery("select * from "+book.getName()+"_Doc_gift where f_isApproved='"+0+"'");
			while(rs.next()){
				库存赠送单po po=new 库存赠送单po();
				po.set单据编号(rs.getString(1));
				po.set客户(rs.getString(2));
				po.set商品编号(rs.getString(3));
				po.set商品名称(rs.getString(4));
				po.set商品型号(rs.getString(5));
				po.set备注(rs.getString("f_remark"));
				po.setNum(rs.getInt(6));
				list.add(po);
			}
	        s.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<DocumentPo> get(DocumentPo from, DocumentPo to) {
		List<DocumentPo> list=new ArrayList<DocumentPo>();
		String from_date;String to_date;
		from_date=from.getDate()==null? null:new SimpleDateFormat("yyyy-MM-dd").format(from.getDate());
		to_date=to.getDate()==null? null:new SimpleDateFormat("yyyy-MM-dd").format(to.getDate());
		String query_from=from_date==null? "":"and f_date>='"+from_date+"'";
		String query_to=to_date==null? "":"and f_date<='"+to_date+"'";
		Connection c=Database.getConnection();
		Statement s;ResultSet rs = null;
		try {
			s = c.createStatement();
			rs=s.executeQuery("select * from "+book.getName()+"_Doc_gift where f_isApproved='"+1+"'"+query_from+query_to);
			while(rs.next()){
				库存赠送单po po=new 库存赠送单po();
				po.set单据编号(rs.getString(1));
				po.set客户(rs.getString(2));
				po.set商品编号(rs.getString(3));
				po.set商品名称(rs.getString(4));
				po.set商品型号(rs.getString(5));
				po.setNum(rs.getInt(6));
				po.set备注(rs.getString("f_remark"));
				list.add(po);
		   }
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	
	//库存赠送单审批后，修改相应的库存数量
	public Po approve(DocumentPo po) {
		String docid=po.get单据编号();
		String id=((库存赠送单po)po).get商品编号();
		long av=0;
		Po m=new Po();int giftNum=0;
		Connection c=Database.getConnection();
		Statement s;
		try {
			s = c.createStatement();
			ResultSet rs2=s.executeQuery("select f_isApproved from "+book.getName()+"_Doc_gift where f_id='"+docid+"'");
			while(rs2.next()){
				if(rs2.getInt(1)==1){
				m.failed("该单据已被审批！");
				return m;
				}
			}
			s.executeUpdate("update "+book.getName()+"_Doc_gift set f_isApproved='"+1+"' where f_id='"+docid+"'");
			
			//库存赠送单审批后，修改相应的库存数量
			ResultSet rs=s.executeQuery("select f_giftNum,f_commodityID from "+book.getName()+"_Doc_gift where f_id='"+docid+"'");
			while(rs.next()){
				giftNum=rs.getInt(1);
				id=rs.getString(2);
			}
			ResultSet rs1=s.executeQuery("select f_stockAveragePrice from "+book.getName()+"_commodity where f_commodityID='"+id+"'");
			while(rs1.next()){
				av=rs1.getLong(1);
			}
			s.execute("update "+book.getName()+"_commodity set f_stockNum=(f_stockNum-"+giftNum+") where f_commodityID='"+id+"'");
			s.execute("update "+book.getName()+"_commodity set f_TotalMoney=(f_totalMoney-f_stockAveragePrice*"+giftNum+") where f_commodityID='"+id+"'");
			s.execute("update "+book.getName()+"_commodity set f_stockAveragePrice=(case when f_stockNum=0 then 0 else f_totalMoney/f_stockNum end)"+" where f_commodityID='"+id+"'");
			
			//添加赠送支出
			经营情况DB db=new 经营情况DB(book);
			Po r1=db.add商品赠出支出(av*giftNum);
			if(!r1.isSuccessed()){
				m.failed("添加商品赠送支出失败！");
				return m;
			}
			s.close();
			m.successed();
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		return m;
	}

	

	


	

}
